* Set current directory and open log
cd "E:\Seagate Sync\VOL\Personal folder\Documents\Research\Current projects\Working party meetings\Posted\EUP 160404"
capture log close
log using "Data management\wpar-management09-reshape", replace text


**********************************************************************
* Generate meeting level and different versions of aggregated datasets
**********************************************************************


* Project and author information
********************************
* Programme:	wpar-management09-reshape.do
* Date:			4 April 2016
* Author:		Frank Haege, University of Limerick
* Contact:		frank.haege@ul.ie 

* Do-file description
*********************
* This do-file finalizes the meeting dataset and generates several aggregated
* datasets. The first of the aggregated datasets aggregates working party meeting 
* duration by month for individual groups (including main and sub-groups). The
* second aggregates montly meeting duration by main group (i.e. it aggregates
* sub-groups). Finally, the third aggregates monthly meeting duration by policy area.


* Clear memory  
**************
version 12.0
clear all
macro drop _all
set more off


* Load data
use "Data management\wpar-management08-merge.dta", clear
des, short


****************************************************************************
* Save disaggregated data with individual meeting dates of individual groups
****************************************************************************

* Check number of working parties
unique wpid
* 437
table wpid, m

* Add working party names for pre-1999, non-offical groups, and other groups
tab wpid if wpname == ""
replace wpname = "General Affairs Meeting" if wpid == "B00"
replace wpname = "Foreign Affairs Meeting" if wpid == "C00"
replace wpname = "Development Meeting" if wpid == "D00"
replace wpname = "Economic and Financial Affairs Meeting" if wpid == "F00"
replace wpname = "Justice and Home Affairs Meeting" if wpid == "G00"
replace wpname = "Agriculture Meeting" if wpid == "H00"
replace wpname = "Competitiveness Meeting" if wpid == "K00"
replace wpname = "Telecommunications Meeting" if wpid == "L00"
replace wpname = "Environment Meeting" if wpid == "R00"
replace wpname = "Ad Hoc Working Party on POSEICAN, POSEIMA, and POSEIDOM" if wpid == "B28"
replace wpname = "Working Party on South Africa" if wpid == "C50"
replace wpname = "Justice and Home Affairs Counsellors" if wpid == "G37"
replace wpname = "Friends of Visa Information System" if wpid == "G38"
replace wpname = "Agriculture Counsellors" if wpid == "H46"
replace wpname = "Fisheries Counsellors" if wpid == "I04"
replace wpname = "Transport Counsellors" if wpid == "Q05"

* Generate running number variable for working party
sort wpid
egen wpnumber = group(wpid)
label var wpnumber "WP number"
labmask wpnumber, values(wpname)
order wpnumber, before(wpname)

* Recode and label monthly meeting days by policy area variable
replace dur = dur/2
label var dur "Meeting days"

* Save dataset with individual meeting dates of particular groups as observations
compress
sort date slot wpid
des, s
save "Output data\wpar-management09-reshape01a-dates.dta", replace


****************************************************************
* Aggregate meeting duration data by month for individual groups
****************************************************************

* Generate new month date variable
generate datem = ym(year, month)
label var datem "Month-year"
format datem %tm
order datem, after(date)	

* Collapse data by month and sub-group
*collapse (sum) dur, by(datem wpid)
tab datem, m
collapse (sum) dur, by(datem wp* s1* s2* month year pres nowp)
des, short
unique wpid
order year month datem dur
label var dur "Meeting days"
sort datem wpid 

* Set data to time-series format
tsset wpnumber datem, monthly
des, s
* 26,479 observations

* Complete panel structure
tsfill, full
des, s
* 20*12 = 240 months*437 working parties = 104,880 observations

* Fill in missing values (i.e. for those months in which no meeting took place)
replace year = year(dofm(datem)) if year == .
replace month = month(dofm(datem)) if month == .
replace dur = 0 if dur == .

gsort wpnumber -wpid datem
by wpnumber: replace nowp = nowp[_n-1] if nowp == .
by wpnumber: replace wpid = wpid[_n-1] if wpid == ""
by wpnumber: replace wppolicy = wppolicy[_n-1] if wppolicy == .
by wpnumber: replace wpsecnew = wpsecnew[_n-1] if wpsecnew == ""
by wpnumber: replace wpsecold = wpsecold[_n-1] if wpsecold == ""
by wpnumber: replace wpname = wpname[_n-1] if wpname == ""
by wpnumber: replace wpbirthdate = wpbirthdate[_n-1] if wpbirthdate == .
by wpnumber: replace wpbirth = wpbirth[_n-1] if wpbirth == .
by wpnumber: replace wpdeathdate = wpdeathdate[_n-1] if wpdeathdate == .
by wpnumber: replace wpdeath = wpdeath[_n-1] if wpdeath == .
by wpnumber: replace wplcensor = wplcensor[_n-1] if wplcensor == .
by wpnumber: replace wpcod = wpcod[_n-1] if wpcod == .
by wpnumber: replace wpsuccwp = wpsuccwp[_n-1] if wpsuccwp == .
by wpnumber: replace wpsuccwp2 = wpsuccwp2[_n-1] if wpsuccwp2 == .
by wpnumber: replace wpbirthdoc = wpbirthdoc[_n-1] if wpbirthdoc == ""
by wpnumber: replace wpcomment = wpcomment[_n-1] if wpcomment == ""

* Drop successor variables
drop s1* s2*

tab wpsecold wpsecnew, m
replace wpsecold = substr(wpid, 1, 1) if wpsecold == ""
replace wpsecnew = "B" if wpsecold == "B"
replace wpsecnew = "C" if wpsecold == "C" | wpsecold == "D"
replace wpsecnew = "D" if wpsecold == "E" | wpsecold == "F"
replace wpsecnew = "E" if wpsecold == "G"
replace wpsecnew = "F" if wpsecold == "H" | wpsecold == "I"
replace wpsecnew = "G" if wpsecold == "J" | wpsecold == "K" | wpsecold == "O"
replace wpsecnew = "H" if wpsecold == "L" | wpsecold == "M" | wpsecold == "N" | wpsecold == "Q"
replace wpsecnew = "I" if wpsecold == "K" | wpsecold == "P" | wpsecold == "S"
replace wpsecnew = "J" if wpsecold == "R"
replace wpsecnew = "K" if wpsecold == "T"

* Generate presidency-month variable
generate presmonth = month if month < 7
replace presmonth = month - 6 if month >= 7
label var presmonth "Presidency Month"
label def presmonthl 1 "Month 1" 2 "Month 2" 3 "Month 3" 4 "Month 4" 5 "Month 5" 6 "Month 6", modify
label val presmonth presmonthl
tab presmonth, m
tab presmonth month

* Generate semester variable
sort year month wppolicy
generate sem = 2
replace sem = 1 if month < 7
tab month sem, m
label var sem "Semester"
label def seml 1 "Spring" 2 "Autumn"
label val sem seml

* Generate year-semester variable
generate yearsem = yh(year, sem)
format yearsem %th
label var yearsem "Year-Semester"
tab yearsem, m
tab yearsem sem, m

* Generate presidency variable
replace pres = yearsem-69
tab pres, m
tab pres yearsem, m

* Save dataset with meeting data aggregated by month for each sub-group
order yearsem sem, before(dur)
order presmonth, after(pres)
sort datem wpid
des, short
save "Output data\wpar-management09-reshape01b-monthsub.dta", replace


******************************************************
* Aggregate montly meeting duration data by main group
******************************************************

* Rename sub-group ID variable
rename wpid sgid

* Generate main group ID variable
generate id = ""
replace id = substr(sgid, 1, 3)
label var id "WP code"
order id, before(sgid)
unique id
* 261 Working Parties

* Save the variable labels
foreach v of var dur nowp wp* {
	local l`v' : variable label `v'
}

* Collapse data by working party
sort sgid
collapse (sum) dur (first) nowp wp*, by(datem id year month yearsem sem pres presmonth) fast
des, short
* 240 months*261 working parties = 62,640 observations
unique id

* Assign the saved variable labels
foreach v of var dur nowp wp* {
	label var `v' "`l`v''"
}

* Relabel variables
foreach v of var nowp wpbirthdate wpdeathdate wplcensor wpsuccwp wpsuccwp2 {
	label val `v' yesno
}
labmask wpnumber, values(wpname)
label def codl /*
	*/ 0 "Not abolished" /*
	*/ 1 "Abolished" /*
	*/ 2 "Subsumed" /*
	*/ 3 "Merged" /*
	*/ 4 "Downgraded" /*
	*/ 5 "Reallocated" /*
	*/ 6 "Extended" /*
	*/ 7 "Upgraded", modify
label val wpcod codl
label def wppolicyl /*
	*/ 1 "General Affairs" /*
	*/ 2 "Foreign Affairs" /*
	*/ 3 "Development" /*
	*/ 4 "Budget" /*
	*/ 5 "Econ. & Financial Affairs" /*
	*/ 6 "Justice & Home Affairs" /*
	*/ 7 "Agriculture" /*
	*/ 8 "Fisheries" /*
	*/ 9 "Internal Market" /*
	*/ 10 "Telecommunications" /*
	*/ 11 "Energy" /*
	*/ 12 "Research" /*
	*/ 13 "Employment & Soc. Policy" /*
	*/ 14 "Transport" /*
	*/ 15 "Environment" /*
	*/ 16 "Health & Consumer Policy" /*
	*/ 17 "Educ., Youth & Culture", modify
label val wppolicy wppolicyl

* Check coding
tab month sem, m
tab yearsem sem, m
tab pres yearsem, m
tab presmonth month


* Save dataset with meeting data aggregated by month and main group
order dur, before(yearsem)
order id, before(wppolicy)
compress
sort datem id
des, s
save "Output data\wpar-management09-reshape01c-monthmg.dta", replace


*******************************************************
* Aggregate montly meeting duration data by policy area
*******************************************************

* Collapse data by policy area
collapse (sum) dur , by(datem wppolicy year month yearsem sem pres presmonth) fast
des, short
* 240 months*17 policy areas = 4,080 observations
label var dur "Meeting days"
order year month datem dur

* Save dataset with meeting data aggregated by policy area
compress
sort datem wppolicy
des, s
save "Output data\wpar-management09-reshape01d-monthpolarea.dta", replace

* Exit do-file
log close
exit
